﻿using Dt261.jsonmodel;
using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Dt261.db
{
    class SpebechItemDb
    {

        private readonly SQLiteConnection connection;

        public SpebechItemDb(string dbPath)
        {
            connection = new SQLiteConnection(dbPath);
            if(connection.CreateTable<SpeechItem>() > 0)
            {
                string sql = "INSERT INTO `pd_testtable` VALUES ('1000', '单音节', '单音节练习表', 0, 'user/audio', 'single', 'lianxi', 0);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('1001', '单音节', '单音节测试表1', 1, 'user/audio', 'single', 'biao1', 0);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('1005', '单音节', '单音节测试表5', 2, 'user/audio', 'single', 'biao5', 0);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('1006', '单音节', '单音节测试表6', 3, 'user/audio', 'single', 'biao6', 0);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('1007', '单音节', '单音节测试表7', 4, 'user/audio', 'single', 'biao7', 0);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('1008', '单音节', '单音节测试表8', 5, 'user/audio', 'single', 'biao8', 0);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('1009', '单音节', '单音节测试表9', 6, 'user/audio', 'single', 'biao9', 0);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('1010', '缺hu-单音节', '单音节测试表10', 7, 'user/audio', 'single', 'biao10', 0);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2000', '双音节', '双音节练习表', 0, 'user/audio', 'double', 'lianxi', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2001', '双音节', '双音节测试表1', 1, 'user/audio', 'double', 'biao1', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2002', '双音节', '双音节测试表2', 2, 'user/audio', 'double', 'biao2', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2003', '双音节', '双音节测试表3', 3, 'user/audio', 'double', 'biao3', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2004', '双音节', '双音节测试表4', 4, 'user/audio', 'double', 'biao4', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2006', '双音节', '双音节测试表6', 5, 'user/audio', 'double', 'biao6', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2007', '双音节', '双音节测试表7', 6, 'user/audio', 'double', 'biao7', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2008', '双音节', '双音节测试表8', 7, 'user/audio', 'double', 'biao8', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2009', '双音节', '双音节测试表9', 8, 'user/audio', 'double', 'biao9', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('2010', '双音节', '双音节测试表10', 9, 'user/audio', 'double', 'biao10', 1);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3000', '语句', '语句练习表', 0, 'user/audio', 'statement', 'lianxi', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3001', '语句', '语句测试表1', 1, 'user/audio', 'statement', 'biao1', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3002', '语句', '语句测试表2', 2, 'user/audio', 'statement', 'biao2', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3004', '语句', '语句测试表4', 3, 'user/audio', 'statement', 'biao4', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3006', '语句', '语句测试表6', 4, 'user/audio', 'statement', 'biao6', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3007', '语句', '语句测试表7', 5, 'user/audio', 'statement', 'biao7', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3008', '语句', '语句测试表8', 6, 'user/audio', 'statement', 'biao8', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3012', '语句', '语句测试表12', 7, 'user/audio', 'statement', 'biao12', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3013', '语句', '语句测试表13', 8, 'user/audio', 'statement', 'biao13', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3014', '语句', '语句测试表14', 9, 'user/audio', 'statement', 'biao14', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3015', '语句', '语句测试表15', 10, 'user/audio', 'statement', 'biao15', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3016', '语句', '语句测试表16', 11, 'user/audio', 'statement', 'biao16', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3018', '语句', '语句测试表18', 12, 'user/audio', 'statement', 'biao18', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3021', '语句', '语句测试表21', 13, 'user/audio', 'statement', 'biao21', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3022', '语句', '语句测试表22', 14, 'user/audio', 'statement', 'biao22', 2);\n" +
                             "INSERT INTO `pd_testtable` VALUES ('3025', '语句', '语句测试表25', 15, 'user/audio', 'statement', 'biao25', 2);\n";


                SQLiteCommand command;
                char[] s = { '\n' };
                String[] array = sql.Split(s);

                for (int i = 0; i < array.Length; i++)
                {
                    command = connection.CreateCommand(array[i]);

                    command.ExecuteNonQuery();
                }
            }
        }

        public int AddSpeechItem(SpeechItem m)
        {
            if (string.IsNullOrWhiteSpace(m.id))
            {
                throw new Exception(" id are required!");
                //return 0;
            }
            // 

            return connection.Insert(m);
        }

        //public int UpdateMember(MemberInfo m)
        //{
        //    if (string.IsNullOrWhiteSpace(m.Name))
        //        throw new Exception(" name  are required!");

        //    IList<MemberInfo> list = connection.Query<MemberInfo>("select * from memberinfo where id = ?", m.Id);

        //    if (list.Count() > 0)
        //    {
        //        return connection.Update(m);
        //    }
        //    else
        //    {
        //        return this.AddMember(m);
        //    }
        //}

        public IList<SpeechItem> GetSpeechItems()
            => connection.Table<SpeechItem>().ToList();

        public IList<SpeechItem> GetSpeechItemsByUid(string uid)
        {
            IList<SpeechItem> list = connection.Query<SpeechItem>("select * from pd_testtable where id = ?", uid);

            return list;
        }

        public IList<SpeechItem> GetSpeechItemsByType(string type)
        {
            IList<SpeechItem> list = connection.Query<SpeechItem>("select * from pd_testtable where testTableType = ?", type);

            return list;
        }

        //public IList<MemberInfo> GetMembersByUserId(string userid)
        //{
        //    IList<MemberInfo> list = connection.Query<MemberInfo>("select * from memberinfo where UserId = ?  order by createTime DESC", userid);

        //    return list;
        //}

        //public MemberInfo GetOneMemberByUid(string uid)
        //{
        //    IList<MemberInfo> list = GetMemberByUid(uid);

        //    return list.Count() > 0 ? list[0] : null;
        //}

        //public MemberInfo GetOneMemberById(int id)
        //{
        //    IList<MemberInfo> list = GetMemberById(id);

        //    return list.Count() > 0 ? list[0] : null;
        //}

        //public int DeleteMemberInfo(MemberInfo m)
        //{
        //    if (m != null)
        //        return connection.Delete(m);
        //    return 0;
        //}

        //public IList<MemberInfo> SearchMemberInfo(string userid, string key)
        //{
        //    if (key != null)
        //    {
        //        if (key == "ALL")
        //        {
        //            IList<MemberInfo> list = connection.Query<MemberInfo>("select * from memberinfo where name like ?  order by createTime DESC", "%" + "%");
        //            return list;
        //        }
        //        else
        //        {
        //            IList<MemberInfo> list = connection.Query<MemberInfo>("select * from memberinfo where name like ? and  UserId = ?  order by createTime DESC", "%" + key + "%", userid);
        //            return list;
        //        }

        //    }

        //    return null;
        //}
    }
}

